from sqlalchemy import Column, Integer, String, Text, DateTime, Boolean, Float, Enum, ForeignKey, Index, JSON
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
import enum

from app.shared.constants.enums import (
    UserRole, DeviceStatus, DeviceType, WorkOrderStatus, 
    WorkOrderPriority, WorkOrderType, AlertLevel, AlertType
)

Base = declarative_base()


class User(Base):
    """用户表"""
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    username = Column(String(50), unique=True, nullable=False, index=True)
    email = Column(String(100), unique=True, nullable=True, index=True)
    phone = Column(String(20), unique=True, nullable=True, index=True)
    password_hash = Column(String(255), nullable=False)
    full_name = Column(String(100), nullable=True)
    role = Column(Enum(UserRole), default=UserRole.USER, nullable=False, index=True)
    avatar_url = Column(String(255), nullable=True)
    is_active = Column(Boolean, default=True, nullable=False)
    last_login_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=func.now(), nullable=False)
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), nullable=False)
    
    # 关系
    created_work_orders = relationship("WorkOrder", foreign_keys="WorkOrder.creator_id", back_populates="creator")
    assigned_work_orders = relationship("WorkOrder", foreign_keys="WorkOrder.assignee_id", back_populates="assignee")
    resolved_alerts = relationship("Alert", back_populates="resolver")
    
    def __repr__(self):
        return f"<User(id={self.id}, username='{self.username}', role='{self.role}')>"


class Device(Base):
    """设备表"""
    __tablename__ = "devices"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    device_code = Column(String(50), unique=True, nullable=False, index=True)
    device_name = Column(String(100), nullable=False)
    device_type = Column(Enum(DeviceType), nullable=False, index=True)
    location_name = Column(String(100), nullable=True)
    latitude = Column(Float, nullable=True)
    longitude = Column(Float, nullable=True)
    status = Column(Enum(DeviceStatus), default=DeviceStatus.OFFLINE, nullable=False, index=True)
    materials_level = Column(Integer, default=100, nullable=True)  # 百分比
    last_maintenance_at = Column(DateTime, nullable=True)
    next_maintenance_at = Column(DateTime, nullable=True)
    last_seen_at = Column(DateTime, nullable=True)  # 最后在线时间
    firmware_version = Column(String(50), nullable=True)  # 固件版本
    hardware_version = Column(String(50), nullable=True)  # 硬件版本
    install_date = Column(DateTime, nullable=True)  # 安装日期
    warranty_date = Column(DateTime, nullable=True)  # 保修日期
    created_at = Column(DateTime, default=func.now(), nullable=False)
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), nullable=False)
    
    # 索引
    __table_args__ = (
        Index('idx_device_location', latitude, longitude),
        Index('idx_device_status_type', status, device_type),
    )
    
    # 关系
    work_orders = relationship("WorkOrder", back_populates="device")
    alerts = relationship("Alert", back_populates="device")
    sensor_data = relationship("SensorData", back_populates="device")
    
    def __repr__(self):
        return f"<Device(id={self.id}, code='{self.device_code}', type='{self.device_type}')>"


class WorkOrder(Base):
    """工单表"""
    __tablename__ = "work_orders"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    order_number = Column(String(50), unique=True, nullable=False, index=True)
    device_id = Column(Integer, ForeignKey("devices.id", ondelete="CASCADE"), nullable=False)
    creator_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    assignee_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    title = Column(String(200), nullable=False)
    description = Column(Text, nullable=True)
    priority = Column(Enum(WorkOrderPriority), default=WorkOrderPriority.MEDIUM, nullable=False, index=True)
    status = Column(Enum(WorkOrderStatus), default=WorkOrderStatus.PENDING, nullable=False, index=True)
    work_type = Column(Enum(WorkOrderType), nullable=False, index=True)
    estimated_duration = Column(Integer, nullable=True)  # 预计工时(分钟)
    actual_duration = Column(Integer, nullable=True)     # 实际工时(分钟)
    fault_reason = Column(String(500), nullable=True)    # 故障原因
    solution = Column(Text, nullable=True)               # 解决方案
    images = Column(JSON, nullable=True)                 # 图片列表
    scheduled_at = Column(DateTime, nullable=True)       # 计划执行时间
    started_at = Column(DateTime, nullable=True)         # 开始时间
    completed_at = Column(DateTime, nullable=True)       # 完成时间
    created_at = Column(DateTime, default=func.now(), nullable=False, index=True)
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), nullable=False)
    
    # 索引
    __table_args__ = (
        Index('idx_workorder_device_status', device_id, status),
        Index('idx_workorder_assignee_status', assignee_id, status),
        Index('idx_workorder_priority_created', priority, created_at),
    )
    
    # 关系
    device = relationship("Device", back_populates="work_orders")
    creator = relationship("User", foreign_keys=[creator_id], back_populates="created_work_orders")
    assignee = relationship("User", foreign_keys=[assignee_id], back_populates="assigned_work_orders")
    
    def __repr__(self):
        return f"<WorkOrder(id={self.id}, number='{self.order_number}', status='{self.status}')>"


class Alert(Base):
    """告警表"""
    __tablename__ = "alerts"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    device_id = Column(Integer, ForeignKey("devices.id", ondelete="CASCADE"), nullable=False)
    alert_type = Column(Enum(AlertType), nullable=False, index=True)
    level = Column(Enum(AlertLevel), default=AlertLevel.WARNING, nullable=False, index=True)
    title = Column(String(200), nullable=False)
    message = Column(Text, nullable=True)
    data = Column(JSON, nullable=True)  # 告警相关数据
    is_resolved = Column(Boolean, default=False, nullable=False, index=True)
    resolved_by = Column(Integer, ForeignKey("users.id"), nullable=True)
    resolved_at = Column(DateTime, nullable=True)
    auto_resolved = Column(Boolean, default=False, nullable=False)  # 是否自动解决
    created_at = Column(DateTime, default=func.now(), nullable=False, index=True)
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), nullable=False)
    
    # 索引
    __table_args__ = (
        Index('idx_alert_device_level', device_id, level),
        Index('idx_alert_type_resolved', alert_type, is_resolved),
        Index('idx_alert_created_resolved', created_at, is_resolved),
    )
    
    # 关系
    device = relationship("Device", back_populates="alerts")
    resolver = relationship("User", back_populates="resolved_alerts")
    
    def __repr__(self):
        return f"<Alert(id={self.id}, type='{self.alert_type}', level='{self.level}')>"


class SensorData(Base):
    """传感器数据表"""
    __tablename__ = "sensor_data"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    device_id = Column(Integer, ForeignKey("devices.id", ondelete="CASCADE"), nullable=False)
    timestamp = Column(DateTime, nullable=False, index=True)
    data_type = Column(String(50), nullable=False)  # 传感器类型
    value = Column(Float, nullable=False)           # 传感器值
    unit = Column(String(20), nullable=True)        # 单位
    quality = Column(Integer, default=100, nullable=False)  # 数据质量 0-100
    created_at = Column(DateTime, default=func.now(), nullable=False)
    
    # 索引
    __table_args__ = (
        Index('idx_sensor_device_timestamp', device_id, timestamp),
        Index('idx_sensor_type_timestamp', data_type, timestamp),
    )
    
    # 关系
    device = relationship("Device", back_populates="sensor_data")
    
    def __repr__(self):
        return f"<SensorData(device_id={self.device_id}, type='{self.data_type}', value={self.value})>"


class Notification(Base):
    """通知表"""
    __tablename__ = "notifications"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    title = Column(String(200), nullable=False)
    message = Column(Text, nullable=False)
    type = Column(String(50), nullable=False, index=True)  # 通知类型
    data = Column(JSON, nullable=True)  # 通知相关数据
    is_read = Column(Boolean, default=False, nullable=False, index=True)
    read_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=func.now(), nullable=False, index=True)
    
    # 索引
    __table_args__ = (
        Index('idx_notification_user_read', user_id, is_read),
        Index('idx_notification_type_created', type, created_at),
    )
    
    def __repr__(self):
        return f"<Notification(id={self.id}, user_id={self.user_id}, type='{self.type}')>"


class DeviceStatistics(Base):
    """设备统计表"""
    __tablename__ = "device_statistics"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    device_id = Column(Integer, ForeignKey("devices.id", ondelete="CASCADE"), nullable=False)
    date = Column(DateTime, nullable=False, index=True)  # 统计日期
    usage_count = Column(Integer, default=0, nullable=False)  # 使用次数
    usage_duration = Column(Integer, default=0, nullable=False)  # 使用时长(分钟)
    maintenance_count = Column(Integer, default=0, nullable=False)  # 维护次数
    fault_count = Column(Integer, default=0, nullable=False)  # 故障次数
    avg_materials_level = Column(Float, nullable=True)  # 平均耗材余量
    created_at = Column(DateTime, default=func.now(), nullable=False)
    
    # 索引
    __table_args__ = (
        Index('idx_device_stats_device_date', device_id, date),
    )
    
    def __repr__(self):
        return f"<DeviceStatistics(device_id={self.device_id}, date='{self.date}')>"


class UserSession(Base):
    """用户会话表"""
    __tablename__ = "user_sessions"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    session_token = Column(String(255), unique=True, nullable=False, index=True)
    refresh_token = Column(String(255), unique=True, nullable=True, index=True)
    device_info = Column(JSON, nullable=True)  # 设备信息
    ip_address = Column(String(45), nullable=True)  # IP地址
    user_agent = Column(Text, nullable=True)  # 用户代理
    is_active = Column(Boolean, default=True, nullable=False)
    expires_at = Column(DateTime, nullable=False, index=True)
    created_at = Column(DateTime, default=func.now(), nullable=False)
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), nullable=False)
    
    def __repr__(self):
        return f"<UserSession(id={self.id}, user_id={self.user_id})>"